import pandas as pd
import ast

# 读取原始数据
df = pd.read_excel('文档/下载档案.xlsx')

# 创建新的DataFrame
new_data = []

# 遍历原始数据
for index, row in df.iterrows():
    try:
        # 解析疾病史数据
        disease_history = ast.literal_eval(row['疾病史'])
    except ValueError:
        print(f"在索引{index}处的疾病史值无法解析：{row['疾病史']}")
        # 初始化疾病一到疾病五
        disease_one = ''
        disease_two = ''
        disease_three = ''
        disease_four = ''
        disease_five = ''  # 慢性阻塞性肺疾病
        # 将数据添加到新的列表
        new_data.append([row['身份证号'], disease_one, disease_two, disease_three, disease_four, disease_five])
        continue

    # 初始化疾病一到疾病五
    disease_one = ''
    disease_two = ''
    disease_three = ''
    disease_four = ''
    disease_five = ''  # 慢性阻塞性肺疾病

    # 检查是否有各种疾病
    for disease in disease_history:
        for disease1 in disease:
            if "高血压" in disease1:
                disease_one = '高血压'
            if "糖尿病" in disease1:
                disease_two = '糖尿病'
            if "冠心病" in disease1:
                disease_three = '冠心病'
            if "脑卒中" in disease1:
                disease_four = '脑卒中'
            if "慢性阻塞性肺疾病" in disease1:
                disease_five = '慢性阻塞性肺疾病'

    # 将数据添加到新的列表
    new_data.append([row['身份证号'], disease_one, disease_two, disease_three, disease_four, disease_five])

# 从新的列表创建DataFrame
new_df = pd.DataFrame(new_data, columns=['身份证号', '疾病一', '疾病二', '疾病三', '疾病四', '疾病五'])

# 保存新的DataFrame到Excel
new_df.to_excel('文档/档约比对.xlsx', index=False)

print('程序执行完成')
